using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using CommonLibrary.DBUtility;
using CommonLibrary.Model;

namespace CommonLibrary.DAL
{
    public class UserInfo
    {
        public bool Exsits(int UserID)
        {
            SqlParameter[] parameters = {
                new SqlParameter("@UserID",SqlDbType.Int)
            };
            parameters[0].Value = UserID;
            using (SqlConnection conn = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlDataReader dr = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, "UserInfo_Exsits", parameters);
                if (dr.Read())
                    return true;
                else
                    return false;
            }
        }
        public bool Insert(UserInfoInfo UserInfo)
        {
            SqlParameter[] parameters = {
                new SqlParameter("@UserName",SqlDbType.NVarChar,50),
                new SqlParameter("@Password",SqlDbType.NVarChar,50),
                new SqlParameter("@ProvinceID",SqlDbType.VarChar,500),
                new SqlParameter("@CityID",SqlDbType.VarChar,500),
                new SqlParameter("@StartTime",SqlDbType.DateTime),
                new SqlParameter("@EndTime",SqlDbType.DateTime),
                new SqlParameter("@Locked",SqlDbType.Bit),
                new SqlParameter("@Description",SqlDbType.NVarChar,200)
            };
            parameters[0].Value = UserInfo.UserName;
            parameters[1].Value = UserInfo.Password;
            parameters[2].Value = UserInfo.ProvinceID;
            parameters[3].Value = UserInfo.CityID;
            parameters[4].Value = UserInfo.StartTime;
            parameters[5].Value = UserInfo.EndTime;
            parameters[6].Value = UserInfo.Locked;
            parameters[7].Value = UserInfo.Description;
            using (SqlConnection conn = new SqlConnection(SqlHelper.CONN_STRING))
            {
                int rowAffected = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "UserInfo_Insert", parameters);
                return rowAffected > 0;
            }
        }
        public bool Update(UserInfoInfo UserInfo)
        {
            SqlParameter[] parameters = {
                new SqlParameter("@UserID",SqlDbType.Int),
                new SqlParameter("@UserName",SqlDbType.NVarChar,50),
                new SqlParameter("@Password",SqlDbType.NVarChar,50),
                new SqlParameter("@ProvinceID",SqlDbType.VarChar,500),
                new SqlParameter("@CityID",SqlDbType.VarChar,500),
                new SqlParameter("@StartTime",SqlDbType.DateTime),
                new SqlParameter("@EndTime",SqlDbType.DateTime),
                new SqlParameter("@Locked",SqlDbType.Bit),
                new SqlParameter("@Description",SqlDbType.NVarChar,200)
            };
            parameters[0].Value = UserInfo.UserID;
            parameters[1].Value = UserInfo.UserName;
            parameters[2].Value = UserInfo.Password;
            parameters[3].Value = UserInfo.ProvinceID;
            parameters[4].Value = UserInfo.CityID;
            parameters[5].Value = UserInfo.StartTime;
            parameters[6].Value = UserInfo.EndTime;
            parameters[7].Value = UserInfo.Locked;
            parameters[8].Value = UserInfo.Description;
            using (SqlConnection conn = new SqlConnection(SqlHelper.CONN_STRING))
            {
                int rowAffected = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "UserInfo_Update", parameters);
                return rowAffected > 0;
            }
        }
        public bool Delete(int UserID)
        {
            SqlParameter[] parameters = {
                new SqlParameter("@UserID",SqlDbType.Int)
            };
            parameters[0].Value = UserID;
            using (SqlConnection conn = new SqlConnection(SqlHelper.CONN_STRING))
            {
                int rowAffected = SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "UserInfo_Delete", parameters);
                return rowAffected > 0;
            }
        }
        public UserInfoInfo GetUserInfo(int UserID)
        {
            UserInfoInfo UserInfo = null;
            SqlParameter[] parameters = {
                new SqlParameter("@UserID",SqlDbType.Int)
            };
            parameters[0].Value = UserID;
            using (SqlConnection conn = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlDataReader dr = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, "UserInfo_GetDetail", parameters);
                if (dr.Read())
                {
                    UserInfo = new UserInfoInfo();
                    UserInfo.UserID = dr.GetInt32(0);
                    UserInfo.UserName = dr.GetString(1);
                    UserInfo.Password = dr.GetString(2);
                    UserInfo.ProvinceID = dr.GetString(3);
                    UserInfo.CityID = dr.GetString(4);
                    UserInfo.StartTime = dr.GetDateTime(5);
                    UserInfo.EndTime = dr.GetDateTime(6);
                    UserInfo.Locked = dr.GetBoolean(7);
                    UserInfo.Description = dr.GetString(8);
                }
                return UserInfo;
            }
        }
        public List<UserInfoInfo> GetList(string StrWhere)
        {
            List<UserInfoInfo> UserInfos = new List<UserInfoInfo>();
            UserInfoInfo UserInfo = null;
            SqlParameter[] parameters = {
                new SqlParameter("@StrWhere",SqlDbType.NVarChar,4000)
            };
            parameters[0].Value = StrWhere;
            using (SqlConnection conn = new SqlConnection(SqlHelper.CONN_STRING))
            {
                SqlDataReader dr = SqlHelper.ExecuteReader(conn, CommandType.StoredProcedure, "UserInfo_GetList", parameters);
                while (dr.Read())
                {
                    UserInfo = new UserInfoInfo();
                    UserInfo.UserID = dr.GetInt32(0);
                    UserInfo.UserName = dr.GetString(1);
                    UserInfo.Password = dr.GetString(2);
                    UserInfo.ProvinceID = dr.GetString(3);
                    UserInfo.CityID = dr.GetString(4);
                    UserInfo.StartTime = dr.GetDateTime(5);
                    UserInfo.EndTime = dr.GetDateTime(6);
                    UserInfo.Locked = dr.GetBoolean(7);
                    UserInfo.Description = dr.GetString(8);
                    UserInfos.Add(UserInfo);
                }
                return UserInfos;
            }
        }
    }
}